﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySoftWare.BaseModel;
using System.Data.Common;
using MySoftWare.DataAccess;
using System.Data;
using System.Data.SqlClient;
using MySoftWare.Component.Tools.Utils;

namespace MySoftWare.DataCommon
{
    /// <summary>
    /// 用户和用户组相关业务处理  added by lqm 2013.06.06
    /// </summary>
    public class UserAccessor
    {
        /// <summary>
        /// 获取所有用户组列表
        /// </summary>
        /// <returns></returns>
        private const string Sql_GetUserGroup = @"select GroupID,GroupName,Remark from Sys_UserGroup";
        public static List<UserGroupModel> GetUserGroup()
        {
            List<UserGroupModel> List = new List<UserGroupModel>();
            DbDataReader Reader = DBHelper.ExecuteReader(Sql_GetUserGroup);
            while (Reader.Read())
            {
                UserGroupModel UserGroup = new UserGroupModel();
                UserGroup.GroupID = Reader.IsDBNull(0) ? 0 : Reader.GetInt32(0);
                UserGroup.GroupName = Reader.IsDBNull(1) ? "" : Reader.GetString(1);
                UserGroup.Remark = Reader.IsDBNull(2) ? "" : Reader.GetString(2);
                List.Add(UserGroup);
            }
            return List;
        }

        /// <summary>
        /// 添加用户组信息
        /// </summary>
        /// <param name="UserGroup"></param>
        /// <returns></returns>
        private const string Sql_AddUserGroup = @"insert into Sys_UserGroup values(@GroupName,@Remark,@AddUser,@AddDate,@UpdateDate,@UpdateUser)";
        public static bool AddUserGroup(UserGroupModel UserGroup)
        {
            DbParameter[] Params = { 
                                        DBHelper.MakeInParam("@GroupName",System.Data.DbType.String,UserGroup.GroupName),
                                        DBHelper.MakeInParam("@Remark",System.Data.DbType.String,UserGroup.Remark),
                                        DBHelper.MakeInParam("@AddUser",System.Data.DbType.String,UserGroup.AddUser),
                                        DBHelper.MakeInParam("@AddDate",System.Data.DbType.DateTime,UserGroup.AddDate),
                                        DBHelper.MakeInParam("@UpdateUser",System.Data.DbType.String,UserGroup.UpdateUser),
                                        DBHelper.MakeInParam("@UpdateDate",System.Data.DbType.DateTime,UserGroup.UpdateDate)
                                   };
            int count=DBHelper.ExecuteNonQuery(CommandType.Text,Sql_AddUserGroup,Params);
            return (count > 0);
        }

        /// <summary>
        /// 删除用户组信息  (暂用事务)
        /// </summary>
        /// <param name="DeleteUserGroupID">参数可能为 3,4,5</param>
        /// <returns></returns>
        private const string Sql_DeleteUserGroup = @"delete from Sys_UserGroup where GroupID=@GroupID";
        //@"delete from Sys_UserGroup where GroupID in(3,4,5)";
        public static bool DeleteUserGroup(string DeleteUserGroupID)
        {
            SqlConnection conn = new SqlConnection(DBHelper.ConnectionString);
            conn.Open();
            using (SqlTransaction trans = conn.BeginTransaction())
            {
                string[] GroupListID = DeleteUserGroupID.Split(',');
                try
                {
                    for(int i=0;i<GroupListID.Length;i++)
                    {
                        DbParameter[] Params = { 
                                                    DBHelper.MakeInParam("@GroupID",System.Data.DbType.Int32,GroupListID[i].StrToInt(0))
                                               };
                        DBHelper.ExecuteNonQuery(CommandType.Text, Sql_DeleteUserGroup, Params);
                    }
                    trans.Commit();
                    return true;
                }
                catch(Exception e)
                {
                    trans.Rollback();
                    return false;
                    throw e;
                }
            }
        }


        /// <summary>
        /// 获取用户组详细信息
        /// </summary>
        private const string Sql_GetUserGroupDetail = @"select GroupID,GroupName,Remark from Sys_UserGroup where GroupID=@GroupID";
        public static UserGroupModel GetUserGroupDetail(string GroupID)
        {
            UserGroupModel UserGroup = new UserGroupModel();
            DbParameter[] Params = { 
                                        DBHelper.MakeInParam("@GroupID",DbType.Int32,GroupID.StrToInt(0))
                                   };
            DbDataReader Reader = DBHelper.ExecuteReader(CommandType.Text, Sql_GetUserGroupDetail, Params);
            if (Reader.Read())
            {
                UserGroup.GroupID = Reader.IsDBNull(0) ? 0 : Reader.GetInt32(0);
                UserGroup.GroupName = Reader.IsDBNull(1) ? "" : Reader.GetString(1);
                UserGroup.Remark = Reader.IsDBNull(2) ? "" : Reader.GetString(2);
            }
            return UserGroup;
        }

        private const string Sql_UpdateUserGroup = @"update Sys_UserGroup Set GroupName=@GroupName, Remark=@Remark,AddUser=@AddUser,AddDate=@AddDate,UpdateUser=@UpdateUser,UpdateDate=@UpdateDate where GroupID=@GroupID";
        public static bool UpdateUserGroup(UserGroupModel UserGroup)
        {
            DbParameter[] Params = { 
                                        DBHelper.MakeInParam("@GroupName",DbType.String,UserGroup.GroupName),
                                        DBHelper.MakeInParam("@Remark",DbType.String,UserGroup.Remark),
                                        DBHelper.MakeInParam("@AddUser",DbType.String,UserGroup.AddUser),
                                        DBHelper.MakeInParam("@AddDate",DbType.DateTime,UserGroup.AddDate),
                                        DBHelper.MakeInParam("@UpdateUser",DbType.String,UserGroup.UpdateUser),
                                        DBHelper.MakeInParam("@UpdateDate",DbType.DateTime,UserGroup.UpdateDate),
                                        DBHelper.MakeInParam("@GroupID",DbType.Int32,UserGroup.GroupID)
                                   };
            int count=DBHelper.ExecuteNonQuery(CommandType.Text, Sql_UpdateUserGroup, Params);
            return (count > 0);
        }
    }
}
